package unipi.database;

import java.util.HashMap;
import java.util.List;
import java.util.ArrayList;

import unipi.helpers.ContactTypes;
import unipi.helpers.Pair;

public class Department
{
	public static int Add(String name, String description)
	{
		String columns = "name, description";
		String values = "'" + name + "' , '" + description + "'";
		return SQLMethods.Insert("Departments", columns, values);
	}
	
	public static ErrorCodes Rank(String parentID, String childID)
	{
		String columns = "parentDptID, childDptID";
		String values = "'" + parentID + "' , '" + childID + "'";
		return SQLMethods.Insert("DptHierarchies", columns, values) != -1? ErrorCodes.OK : ErrorCodes.databaseError;		
	}
	
	public static ErrorCodes AddAndRank(String name, String description, String parentID, String userID)
	{
		int childID = Add(name, description);
		
		Rights.giveDptRights(userID, Integer.toString(childID), Rights.RightCols.UserRights);
		Rights.giveDptRights(userID, Integer.toString(childID), Rights.RightCols.DptRights);
		Rights.giveDptRights(userID, Integer.toString(childID), Rights.RightCols.TaskRights);
		
		if(parentID == null)
			return childID != -1? ErrorCodes.OK : ErrorCodes.databaseError;
		else if(childID == -1)
			return ErrorCodes.databaseError;
		else
			return Rank(parentID, Integer.toString(childID));
	}
	

	public static ErrorCodes Edit(String dptID, String newname, String newdesc)
	{
		String[] columns = {"name", "description"};
		String[] values = {"'" + newname + "'", "'" + newdesc + "'"};
		
		return SQLMethods.Update("Departments", columns, values, "id = " + dptID)  != -1? ErrorCodes.OK : ErrorCodes.databaseError;
	}
	
	public static ErrorCodes Delete(String dptID)
	{
		return SQLMethods.Delete("Departments", "id = " + dptID)  != -1? ErrorCodes.OK : ErrorCodes.databaseError;		
	}
	
	public static List<List<String>> getDptInfo(String dptID)
	{
		String query = "select name, description from " +
					   "departments where id = " + dptID;
		
		return SQLMethods.Select(query, 2);
	}
	
	public static List<List<String>> getDpts()
	{
		String query = "select id, name, description from departments order by id";
	
		return SQLMethods.Select(query, 3);
	}
	
	public static List<List<String>>getUsers(String dptID)
	{
		int ncols = 5;
		
		String query = "select u.id, concat(u.surname, ' ', u.name), coalesce(u.position, '-'), u.photo," +
						" coalesce(m.contact, '-') from ActiveUsers u\r\n" + 
						"left join\r\n" + 
						"(\r\n" + 
						"    select userID, contact from ContactInfo\r\n" + 
						"    where type = '" + ContactTypes.MainMail + "'\r\n" +  
						") m\r\n" + 
						"on u.id = m.userID\r\n" + 
						"where dptID = " + dptID;
		
		return SQLMethods.Select(query, ncols);		
	}
	
	public static List<List<String>> getDptHrch()
	{
		String query = "select parentDptID, childDptID from DptHierarchies;";
		
		return SQLMethods.Select(query, 2);		
	}
	
	public static List<List<String>> getChildren(String dptID)
	{		
		String query = "select d.id, d.name, d.description from " +
						"DptHierarchies h inner join Departments d " +
						"on h.childDptID = d.id " +
						"where h.parentDptID = " + dptID;
		
		return SQLMethods.Select(query, 3);		
	}
	
	public static List<Pair<List<String>, Integer>> getAllChildren(String dptID)
	{
		List<Pair<String, Integer>> children = getAllChildrenIDs(dptID);
		
		if(children == null) return null;
		if(children.size() == 0) return null;
		
		String query = "select id, name, description from Departments where ";
		
		boolean firstTime = true;
		for(Pair<String, Integer> child : children)
		{
			if(!firstTime)
				query += " or id = " + child.getFirst();
			else
			{
				query += " id = " + child.getFirst();
				firstTime = false;
			}
		}
		
		List<List<String>> res = SQLMethods.Select(query, 3);
		if(res == null) return null;
		
		HashMap<String, List<String>> childrenMap = new HashMap<String, List<String>>();
		for(List<String> row : res)
		{
			childrenMap.put(row.get(0), row);			
		}		
		
		List<Pair<List<String>, Integer>> allChildren = new ArrayList<Pair<List<String>, Integer>>();
		
		for(Pair<String, Integer> child : children)
		{
			Pair<List<String>, Integer> p = new Pair<List<String>, Integer>(childrenMap.get(child.getFirst()), child.getSecond());
			allChildren.add(p);			
		}
		
		return allChildren;		
	}
	
	public static List<Pair<String, Integer>> getAllChildrenIDs(String dptID)
	{
		String query = "select parentDptID, childDptID from DptHierarchies order by parentDptID";
		
		List<List<String>> tree = SQLMethods.Select(query, 2);
		
		if(tree == null) return null;		
		
		return getSubTree(tree, dptID, 0);
	}
	
	public static List<List<String>> getRootDpts()
	{
		String query = "select id, name, description from Departments\r\n" + 
						"where id in\r\n" + 
						"(\r\n" + 
						"    select distinct parentDptID from DptHierarchies\r\n" + 
						"    where parentDptID not in\r\n" + 
						"    (\r\n" + 
						"        select childDptID from DptHierarchies\r\n" + 
						"    )\r\n" + 
						")\r\n" + 
						"or id not in\r\n" + 
						"(\r\n" + 
						"    select distinct parentDptID from DptHierarchies\r\n" + 
						")\r\n" + 
						"and id not in\r\n" + 
						"(\r\n" + 
						"    select distinct childDptID from DptHierarchies\r\n" + 
						")";
						
		return SQLMethods.Select(query, 3);
	}
	
	
	private static List<Pair<String, Integer>> getSubTree(List<List<String>> tree, String rootID, int depth)
	{
		List<Pair<String, Integer>> childrenIDs = new ArrayList<Pair<String, Integer>>();
		
		for(List<String> row : tree)
		{
			String child;
			int parent = Integer.parseInt(row.get(0));
			int root = Integer.parseInt(rootID);			
			
			if(root == parent)
			{
				child = row.get(1);
				childrenIDs.add(new Pair<String, Integer>(child, depth + 1));	
				childrenIDs.addAll(getSubTree(tree, child, depth + 1));							
			}
			else if(parent > root)
			{
				break;				
			}
			
		}		
		
		return childrenIDs;				
	}	

}
